/*
 * Copyright (C) 2009 Dimitrios C. Michalakos
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

import com.cs.util.db.Database;
import com.cs.util.db.DatabaseEngineEnum;
import com.cs.util.db.representation.XhtmlForm;
import com.cs.util.db.representation.XhtmlTable;
import com.cs.util.db.query.QueryException;
import com.cs.util.db.ioc.IOCException;
import com.cs.util.db.representation.RepresentationException;
import com.cs.util.http.QueryString;
import java.sql.SQLException;
import javax.mail.internet.MimeMultipart;
import javax.servlet.ServletContext;
import javax.ws.rs.GET;
import javax.ws.rs.HeaderParam;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.SecurityContext;
import javax.ws.rs.core.UriInfo;

/**
 *
 * @author dimitris@jmike.gr
 */
@Path("/api")
public class Api {

    private final UriInfo uriInfo;
    private final boolean isSecure;
    private final String appTitle;
    private final String authRealm;
    private final int httpsPort;
    private final Meta meta;

    public Api(
            @Context UriInfo uriInfo,
            @Context SecurityContext securityContext,
            @Context ServletContext servletContext /*@Context HttpServletRequest httpServletRequest*/) {
        this.uriInfo = uriInfo;
        this.isSecure = securityContext.isSecure();
        this.appTitle = servletContext.getInitParameter("app-title");
        this.authRealm = servletContext.getInitParameter("auth-realm");
        this.httpsPort = Integer.parseInt(servletContext.getInitParameter("https-port"));
        this.meta = new Meta(DatabaseEngineEnum.valueOf(servletContext.getInitParameter("db-type")),
                servletContext.getInitParameter("db-host"),
                Integer.parseInt(servletContext.getInitParameter("db-port")),
                servletContext.getInitParameter("db-schema"),
                servletContext.getInitParameter("db-username"),
                servletContext.getInitParameter("db-password"));
    }

    @GET
    @Produces("text/plain; charset=utf-8")
    public Response test() {
        return Response.status(200).entity("/tost 123").build();
    }

//    @Path("/test")
//    @GET
//    @Produces("text/plain; charset=utf-8")
//    public Viewable test2() throws ServletException, IOException {
//        return new Viewable("/test.jsp", "");
//    }
//    @Path("/db/1.0/{account}")
//    @GET
//    @Produces("text/html; charset=utf-8")
//    public Response db_1_0_databases_getXhtml1(
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName) {
//        return c.db_1_0_databases_getXhtml(authHeader, accountName);
//    }
    /**
     * Returns an Xhtml Table containing the databases listed under the specified account.
     * This resource is secured via SSL and can only be displayed to users that belong to the specified account.
     * @param authHeader the BASIC authentication HTTP header containing the current user's credentials.
     * @param accountName the name of the user's parent account.
     * @return
     */
    @Path("/db/1.0/{account}.html")
    @GET
    @Produces("text/html; charset=utf-8")
    public Response db_1_0_database_getXhtmlTable(
            @HeaderParam("AUTHORIZATION") String authHeader,
            @PathParam("account") String accountName) {
//        if (isSecure) {//SSL enabled
        final int accountId = meta.getAccountId(accountName);//get accountId
        if (accountId > 0) {//account exists
            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
                try {
                    /* parse the request */
                    QueryString qs = new QueryString(uriInfo.getQueryParameters());
                    /* connect to database */
                    Database db = meta.getDatabase();
                    /* create a representation */
                    XhtmlTable doc = db.getXhtmlTable("database?accountid=" + String.valueOf(accountId), qs);
                    doc.setTitle("View databases of " + accountName + " - " + appTitle);
                    doc.addAction("edit", "Edit", "./" + accountName + "/{name}.html");
                    doc.addAction("viewTables", "View tables", "./" + accountName + "/{name}/tables.html");
                    doc.addAnchor("insert", "Add New", "./" + accountName + "/addnew.html");
                    final String entity = doc.display();//get entity
                    /* close the representation */
                    doc.close();
                    /* disconnect from database */
                    db.close();
                    /* set response */
                    return Response.status(200).entity(entity).build();
                } catch (SQLException ex) {
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (IOCException ex) {//the input data are invalid
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (QueryException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                } catch (RepresentationException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                }
            } else {//user is not authenticated
                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
            }
        } else {//account does not exist in meta database
            return Response.status(404).build();//"Not Found"
        }
//        } else {//SSL not enabled
//            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
//            return Response.status(301).header("Location", location).build();//"Moved Permanently"
//        }
    }

    /**
     * Returns an Xhtml Form containing the designated database of the specified account.
     * This resource is secured via SSL and can only be displayed to the users of the specified account.
     * @param dbName the name of the database.
     * @param authHeader the BASIC authentication HTTP header containing the current user's credentials.
     * @param accountName the name of the user's parent account.
     * @return
     */
    @Path("/db/1.0/{account}/{database}.html")
    @GET
    @Produces("text/html; charset=utf-8")
    public Response db_1_0_database_getXhtmlForm(
            @PathParam("database") String dbName,
            @HeaderParam("AUTHORIZATION") String authHeader,
            @PathParam("account") String accountName) {
//        if (isSecure) {//SSL enabled
        final int accountId = meta.getAccountId(accountName);//get accountId
        if (accountId > 0) {//account exists
            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
                try {
                    /* parse the request */
                    QueryString qs = new QueryString(uriInfo.getQueryParameters());
                    /* connect to database */
                    Database db = meta.getDatabase();
                    /* create a representation */
                    XhtmlForm doc = db.getXhtmlForm("database?accountid=" + String.valueOf(accountId) + "&name=" + dbName, qs);
                    doc.setTitle("Edit database " + dbName + " of " + accountName + " - " + appTitle);
                    doc.setFormAction(uriInfo.getRequestUri().toString());
                    final String entity = doc.display();//get entity
                    /* close the representation */
                    doc.close();
                    /* disconnect from database */
                    db.close();
                    /* set response */
                    return Response.status(200).entity(entity).build();
                } catch (SQLException ex) {
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (IOCException ex) {//the input data are invalid
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (QueryException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                } catch (RepresentationException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                }
            } else {//user is not authenticated
                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
            }
        } else {//account does not exist in meta database
            return Response.status(404).build();//"Not Found"
        }
//        } else {//SSL not enabled
//            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
//            return Response.status(301).header("Location", location).build();//"Moved Permanently"
//        }
    }

    /**
     * Returns an empty Xhtml Form that can be used for creating a new database.
     * This resource is secured via SSL.
     * @param authHeader the BASIC authentication HTTP header containing the current user's credentials.
     * @param accountName the name of the user's parent account.
     * @return
     */
    @Path("/db/1.0/{account}/addnew.html")
    @GET
    @Produces("text/html; charset=utf-8")
    public Response db_1_0_database_getEmptyXhtmlForm(
            @HeaderParam("AUTHORIZATION") String authHeader,
            @PathParam("account") String accountName) {
//        if (isSecure) {//SSL enabled
        final int accountId = meta.getAccountId(accountName);//get accountId
        if (accountId > 0) {//account exists
            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
                try {
                    /* connect to database */
                    Database db = meta.getDatabase();
                    /* create a representation */
                    XhtmlForm doc = db.getEmptyXhtmlForm("database?accountid=" + String.valueOf(accountId));
                    doc.setTitle("Add new database to " + accountName + " - " + appTitle);
                    doc.setFormAction(uriInfo.getRequestUri().toString());
                    final String entity = doc.display();//get entity
                    /* close the representation */
                    doc.close();
                    /* disconnect from database */
                    db.close();
                    /* set response */
                    return Response.status(200).entity(entity).build();
                } catch (SQLException ex) {
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (IOCException ex) {//the input data are invalid
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (QueryException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                } catch (RepresentationException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                }
            } else {//user is not authenticated
                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
            }
        } else {//account does not exist in meta database
            return Response.status(404).build();//"Not Found"
        }
//        } else {//SSL not enabled
//            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
//            return Response.status(301).header("Location", location).build();//"Moved Permanently"
//        }
    }

    /**
     * Inserts a new database under the specified account.
     * This resource is secured via SSL and can only be displayed to the users of the specified account.
     * @param authHeader the BASIC authentication HTTP header containing the current user's credentials.
     * @param accountName the name of the user's parent account.
     * @param formData the form data encoded as multipart/form-data.
     * @return
     */
    @Path("/db/1.0/{account}/addnew.html")
    @POST
    @Produces("text/html; charset=utf-8")
    public Response db_1_0_database_postMultipartForm(
            @HeaderParam("AUTHORIZATION") String authHeader,
            @PathParam("account") String accountName,
            MimeMultipart formData) {
//        if (isSecure) {//SSL enabled
        final int accountId = meta.getAccountId(accountName);//get accountId
        if (accountId > 0) {//account exists
            if (meta.existsUser(authHeader, accountId)) {//user is authenticateds
                try {
                    /* connect to database */
                    Database db = meta.getDatabase();
                    /* set new record */
                    db.setNew("database?accountid=" + String.valueOf(accountId), formData);
                    /* disconnect from database */
                    db.close();
                    /* set response */
                    return Response.status(201).build();
                } catch (SQLException ex) {
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (IOCException ex) {//the input data are invalid
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (QueryException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                }
            } else {//user is not authenticated
                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
            }
        } else {//account does not exist in meta database
            return Response.status(404).build();//"Not Found"
        }
//        } else {//SSL not enabled
//            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
//            return Response.status(301).header("Location", location).build();//"Moved Permanently"
//        }
    }

    @Path("/db/1.0/{account}/{database}/tables.html")
    @GET
    @Produces("text/html; charset=utf-8")
    public Response db_1_0_table_getXhtmlTable(
            @PathParam("database") String dbName,
            @HeaderParam("AUTHORIZATION") String authHeader,
            @PathParam("account") String accountName) {
//        if (isSecure) {//SSL enabled
        final int accountId = meta.getAccountId(accountName);//get accountId
        if (accountId > 0) {//account exists
            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
                try {
                    /* parse the request */
                    QueryString qs = new QueryString(uriInfo.getQueryParameters());
                    /* connect to database */
                    Database db = meta.getDatabase(dbName, accountId);
                    /* design the sql query */
                    final String sql = "SELECT `TABLE_NAME`, `ENGINE`, `TABLE_ROWS` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '" + db.getSchemaName() + "' AND `TABLE_TYPE` = 'BASE TABLE';";
                    /* create a representation */
                    XhtmlTable doc = db.getXhtmlTable(db.getResultSet(sql));
                    doc.setTitle("Table(s) | Database " + dbName + " | " + accountName + " | " + appTitle);
                    doc.addAction("viewData", "View data", "./{TABLE_NAME}/data.html");
                    final String entity = doc.display();//get entity
                    /* close the representation */
                    doc.close();
                    /* disconnect from database */
                    db.close();
                    /* set response */
                    return Response.status(200).entity(entity).build();
                } catch (SQLException ex) {
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (IOCException ex) {//the input data are invalid
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (RepresentationException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                }
            } else {//user is not authenticated
                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
            }
        } else {//account does not exist in meta database
            return Response.status(404).build();//"Not Found"
        }
//        } else {//SSL not enabled
//            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
//            return Response.status(301).header("Location", location).build();//"Moved Permanently"
//        }
    }

    @Path("/db/1.0/{account}/{database}/{table}/data.html")
    @GET
    @Produces("text/html; charset=utf-8")
    public Response db_1_0_data_getXhtmlTable(
            @PathParam("table") String tableName,
            @PathParam("database") String dbName,
            @HeaderParam("AUTHORIZATION") String authHeader,
            @PathParam("account") String accountName) {
//        if (isSecure) {//SSL enabled
        final int accountId = meta.getAccountId(accountName);//get accountId
        if (accountId > 0) {//account exists
            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
                try {
                    /* parse the request */
                    QueryString qs = new QueryString(uriInfo.getQueryParameters());
                    /* connect to database */
                    Database db = meta.getDatabase(dbName, accountId);
                    /* create a representation */
                    XhtmlTable doc = db.getXhtmlTable(tableName, qs);
                    doc.setTitle("Data : Table " + tableName + " : Database " + dbName + " @" + accountName + " - " + appTitle);
                    final String entity = doc.display();//get entity
                    /* close the representation */
                    doc.close();
                    /* disconnect from database */
                    db.close();
                    /* set response */
                    return Response.status(200).entity(entity).build();
                } catch (SQLException ex) {
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (IOCException ex) {//the input data are invalid
                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
                } catch (QueryException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                } catch (RepresentationException ex) {
                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
                }
            } else {//user is not authenticated
                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
            }
        } else {//account does not exist in meta database
            return Response.status(404).build();//"Not Found"
        }
//        } else {//SSL not enabled
//            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
//            return Response.status(301).header("Location", location).build();//"Moved Permanently"
//        }
    }
    
//    /**
//     * Inserts a new database under the specified account.
//     * This resource is secured via SSL and can only be displayed to the users of the specified account.
//     * @param authHeader the BASIC authentication HTTP header containing the current user's credentials.
//     * @param accountName the name of the user's parent account.
//     * @param formData the form data encoded as multipart/form-data.
//     * @return
//     */
//    @Path("/db/1.0/{account}/{database}.html")
//    @POST
//    @Produces("text/html; charset=utf-8")
//    public Response db_1_0_database_postMultipartForm(
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName,
//            MimeMultipart formData) {
////        if (isSecure) {//SSL enabled
//        final int accountId = meta.getAccountId(accountName);//get accountId
//        if (accountId > 0) {//account exists
//            if (meta.existsUser(authHeader, accountId)) {//user is authenticateds
//                try {
//                    /* connect to database */
//                    Database db = meta.getDatabase();
//                    /* initialize entity */
//                    String entity = null;
//                    try {
//                        /* design the sql */
//                        final String sql = db.getInsertSQL("database?accountid=" + String.valueOf(accountId), formData);
//                        /* execute the sql */
////                        Statement stmt = db.createStatement();
////                        stmt.executeUpdate(sql);
//                        /* set entity */
//                        entity = sql;
//                    } catch (IOCException ex) {//the input data are invalid
//
//                    }
//                    /* disconnect from database */
//                    db.close();
//                    /* set response */
//                    return Response.status(200).entity(entity).build();
//                } catch (SQLException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (QueryException ex) {
//                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
//                }
//            } else {//user is not authenticated
//                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
//            }
//        } else {//account does not exist in meta database
//            return Response.status(404).build();//"Not Found"
//        }
////        } else {//SSL not enabled
////            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
////            return Response.status(301).header("Location", location).build();//"Moved Permanently"
////        }
//    }
//    @Path("/db/1.0/{account}/{database}")
//    @POST
//    @Consumes("application/x-www-form-urlencoded")
//    @Produces("text/html; charset=utf-8")
//    public Response db_1_0_postDatabase_Xhtml(
//            @PathParam("database") String dbName,
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName,
//            MultivaluedMap<String, String> form) {
//        return Response.status(200).entity(form.getFirst("host")).build();
//    }
//    @Path("/db/1.0/{account}/{database}.html")
//    @POST
//    @Consumes("multipart/form-data")
//    @Produces("text/html; charset=utf-8")
//    public Response db_1_0_database_postFormMultipart(
//            MimeMultipart form) throws MessagingException {
//        String s = "";
//        for (int i = 0; i < form.getCount(); i++) {//iterate columns
//            BodyPart field = form.getBodyPart(i);
//            Enumeration headers = field.getAllHeaders();
//            while (headers.hasMoreElements()) {
//                Header h = (Header) headers.nextElement();
//                s = s + h.getName() + " = " + h.getValue() + "<br />";
//            }
//            s = s +  " ___ " + field.getLineCount() + " " + field.getSize() + " " + field.getFileName() + ":" + field.getDescription() + ":" + field.getDisposition() + ":" + field.getContentType() + "<br />";
//        }
//        return Response.status(200).entity(s).build();
//    }
    /**
     * Returns an Xhtml List containing the databases listed under the specified account.
     * This resource is secured via SSL and can only be displayed to the users of the specified account.
     * @param authHeader the BASIC authentication header containing the current user's credentials.
     * @param accountName the name of the user's parent account.
     * @return
     */
//    private Response getDatabaseList_Xhtml(String authHeader, String accountName) {
////        if (isSecure) {//SSL enabled
//        final int accountId = meta.getAccountId(accountName);//get accountId
//        if (accountId > 0) {//account exists
//            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
//                try {
//                    Database db = meta.getDatabase();//open the database
//                        /* design the sql query */
//                    SQLDesigner designer = db.getSQLDesigner();
//                    MultivaluedMap<String, String> parameters = uriInfo.getQueryParameters();//get parameters
//                    final String sql = designer.getSelectSQL("database?accountid=" + String.valueOf(accountId), parameters);
//                    /* execute the sql query - get the results */
//                    final ResultSet rs = db.getResultSet(sql);
//                    /* transform the results */
//                    XhtmlTable representation = db.getXhtmlTable(rs);
//                    representation.addAction("edit", "Edit", "./db/{name}");
//                    representation.addAction("showTables", "Show tables", "./db/{name}/tables");
//                    final String entity = representation.getString();
//                    rs.close();//close the resultset
//                    db.close();//close the database
//                    return Response.status(200).entity(entity).build();
//                } catch (SQLException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (QueryException ex) {
//                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
//                } catch (ParserConfigurationException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (TransformerConfigurationException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (TransformerException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                }
//            } else {//user is not authenticated
//                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
//            }
//        } else {//account does not exist in meta database
//            return Response.status(404).build();//"Not Found"
//        }
////        } else {//SSL not enabled
////            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
////            return Response.status(301).header("Location", location).build();//"Moved Permanently"
////        }
//    }
//
//    @Path("/{account}/db")
//    @GET
//    @ProduceMime("text/html; charset=utf-8")
//    public Response getDatabaseList_Xhtml1(
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName) {
//        return getDatabaseList_Xhtml(authHeader, accountName);
//    }
//
//    @Path("/{account}/db.html")
//    @GET
//    @ProduceMime("text/html; charset=utf-8")
//    public Response getDatabaseList_Xhtml2(
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName) {
//        return getDatabaseList_Xhtml(authHeader, accountName);
//    }

//    private Response getDatabase_Xhtml(String dbName, String authHeader, String accountName) {
////        if (isSecure) {//SSL enabled
//        final int accountId = meta.getAccountId(accountName);//get accountId
//        if (accountId > 0) {//account exists
//            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
//                try {
//                    Database db = meta.getDatabase();//open the database
//                        /* design the sql query */
//                    SQLDesigner designer = db.getSQLDesigner();
//                    MultivaluedMap<String, String> parameters = uriInfo.getQueryParameters();//get parameters
//                    final String sql = designer.getSelectSQL("database?accountid=" + String.valueOf(accountId), parameters);
//                    /* execute the sql query - get the results */
//                    final ResultSet rs = db.getResultSet(sql);
//                    /* transform the results */
//                    Representation representation = db.getRepresentation(rs);
//                    final String entity = representation.getXhtmlForm(appTitle + " - " + "Databases of " + accountName, "");
//                    rs.close();//close the resultset
//                    db.close();//close the database
//                    return Response.status(200).entity(entity).build();
//                } catch (SQLException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (QueryException ex) {
//                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
//                } catch (ParserConfigurationException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (TransformerConfigurationException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (TransformerException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                }
//            } else {//user is not authenticated
//                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
//            }
//        } else {//account does not exist in meta database
//            return Response.status(404).build();//"Not Found"
//        }
////        } else {//SSL not enabled
////            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
////            return Response.status(301).header("Location", location).build();//"Moved Permanently"
////        }
//    }
//
//    @Path("/{account}/db/{database}")
//    @GET
//    @ProduceMime("text/html; charset=utf-8")
//    public Response getDatabase_Xhtml1(
//            @PathParam("database") String dbName,
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName) {
//        return getDatabase_Xhtml(dbName, authHeader, accountName);
//    }
//
//    /**
//     * Returns an Xhtml List containing the rows of the designated table.
//     * This resource is secured via SSL and can only be displayed to the users of the specified account.
//     * @param tableName the name of the table.
//     * @param dbName the name of the parent database.
//     * @param authHeader the BASIC authentication header containing the current user's credentials.
//     * @param accountName the name of the user's parent account.
//     * @return
//     */
//    public Response getDataList_Xhtml(String tableName, String dbName, String authHeader, String accountName) {
////        if (isSecure) {//SSL enabled
//        final int accountId = meta.getAccountId(accountName);//get accountId
//        if (accountId > 0) {//account exists
//            if (meta.existsUser(authHeader, accountId)) {//user is authenticated
//                try {
//                    Database db = meta.getDatabase(dbName, accountId);//open the database
//                    if (db != null) {//database exists
//                        if (db.getDatabaseMetaInfo().exists(tableName)) {//table exists in database
//                                /* design the sql query */
//                            SQLDesigner designer = db.getSQLDesigner();
//                            MultivaluedMap<String, String> parameters = uriInfo.getQueryParameters();//get parameters
//                            final String sql = designer.getSelectSQL(tableName, parameters);
//                            /* execute the sql query - get the results */
//                            final ResultSet rs = db.getResultSet(sql);
//                            /* transform the results */
//                            Representation representation = new Representation(db, rs);
//                            representation.addAction("representation", "View data", "./tables/{TABLE_NAME}/data");
//                            final String entity = representation.getXhtmlTable(appTitle + " - " + "Data in table " + tableName);
//                            rs.close();//close the resultset
//                            db.close();//close the database
//                            return Response.status(200).entity(entity).build();
//                        } else {//table does not exist in database
//                            return Response.status(404).build();//"Not Found"
//                        }
//                    } else {//database does not exist or does not belong to this account
//                        return Response.status(404).build();//"Not Found"
//                    }
//                } catch (SQLException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (QueryException ex) {
//                    return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
//                } catch (ParserConfigurationException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (TransformerConfigurationException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                } catch (TransformerException ex) {
//                    return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//                }
//            } else {//user is not authenticated
//                return Response.status(401).header("WWW-Authenticate", "Basic realm=\"" + authRealm + "\"").build();//"Unauthorized"
//            }
//        } else {//account does not exist in meta database
//            return Response.status(404).build();//"Not Found"
//        }
////        } else {//SSL not enabled
////            final URI location = uriInfo.getRequestUriBuilder().scheme("https").port(httpsPort).build();//Upgrade URI to https
////            return Response.status(301).header("Location", location).build();//"Moved Permanently"
////        }
//    }
//
//    @Path("/{account}/db/{database}/tables/{table}/data")
//    @GET
//    @ProduceMime("text/html; charset=utf-8")
//    public Response getDataList_Xhtml1(
//            @PathParam("table") String tableName,
//            @PathParam("database") String dbName,
//            @HeaderParam("AUTHORIZATION") String authHeader,
//            @PathParam("account") String accountName) {
//        return getDataList_Xhtml(tableName, dbName, authHeader, accountName);
//    }
//
//    public Response getMySqlQuery(String tableName) {
//        try {
//            SQLDesigner designer = new MySqlDesigner();
//            MultivaluedMap<String, String> parameters = uriInfo.getQueryParameters();//get parameters
//            final String sql = designer.getSelectSQL(tableName, parameters);
//            return Response.status(200).entity(sql).build();
//        } catch (SQLException ex) {
//            return Response.status(500).entity(ex.getMessage()).build();//"Internal Server Error"
//        } catch (QueryException ex) {
//            return Response.status(404).entity(ex.getMessage()).build();//"Not Found"
//        }
//    }
//
//    @Path("/sql-designer/mysql/{table}.sql")
//    @GET
//    @ProduceMime("text/plain; charset=utf-8")
//    public Response getMySqlQuery1(
//            @PathParam("table") String tableName) {
//        return getMySqlQuery(tableName);
//    }
}
